#!/usr/bin/perl -w
use strict;
use DBI;
use Data::Dumper;
use XML::Simple;
use MIME::Base64;

my $fatal = "Content-Type: text/plain\n\n$!";
my $database_file = "/var/lib/ifootprints/ifootprints.sqlite";

my $response;
my %vars;
my $dbh;
my $xml = "";


create_db();

if($ENV{'QUERY_STRING'}){
	print "Content-type: text/html\n\n";
	print "<html><head><LINK href='/ifootprints_style.css' rel='stylesheet' type='text/css'><title>My Macs</title></head><body>\n";
	my @v = split(/&/, $ENV{'QUERY_STRING'});
	foreach(@v){
		(my $key, my $val) = split(/=/, $_);
		
		$key=~s/%([0-9a-f]{2})/pack("c",hex($1))/gie;
		$val=~s/%([0-9a-f]{2})/pack("c",hex($1))/gie;

		$vars{$key} = $val;
	}
	if(!exists($vars{'new_state'})){
		my $sth = $dbh->prepare("SELECT * from macs where name = ?");
		$sth->execute($vars{'name'});
		if(my $row = $sth->fetchrow_hashref){
			print 
"<form method='GET'>
<table>
	<tr class='macs'>
		<td  class='macs'>Name: </td><td  class='macs'>" . $row->{'name'}  . "</td>
	</tr>
	<tr class='macs'>
		<td  class='macs'>MAC Address: </td><td class='macs'>" . $row->{'en0_mac_address'}  . "</td>
	</tr>

	<tr class='macs'>
		<td class='macs'>State: </td><td class='macs'><select name='new_state' value='" . $row->{'state'} . "' ><option value='aok'>aok</option><option value='stolen'>stolen</option></select></td>
	</tr>
	<tr class='macs'>
		<td colspan='2' align='center'  class='macs'><input type='submit' value='Update'></td>
	</tr>
</table>

<input type='hidden' name='name' value='" . $row->{'name'} . "' />
<input type='hidden' name='en0_mac_address' value='" . $row->{'en0_mac_address'} . "' />
</form>



";

		}
		else{
			print "Not found....";
		}
		$sth->finish();
	}
	else{
		if($vars{'new_state'} ne 'stolen'){
			$vars{'new_state'} = 'aok';
		}
		my $sth = $dbh->prepare("UPDATE macs set state = ? where name = ? and en0_mac_address = ?");
	        $sth->execute($vars{'new_state'}, $vars{'name'}, $vars{'en0_mac_address'}) or die $!;
		$sth->finish();	
		print "Updated...<br /><br />";
	}

	print "<a href='/cgi-bin/ifootprints_backend.pl'>Return</a></body></html>";

	exit();
}

elsif($ENV{'CONTENT_LENGTH'}){
	read(STDIN,$xml,$ENV{'CONTENT_LENGTH'});
}

else{
	my $i = 0;
	
	print "Content-type: text/html\n\n";
	print "<html><head><LINK href='/ifootprints_style.css' rel='stylesheet' type='text/css'><title>My Macs</title></head><body>\n";
	
	my $sth = $dbh->prepare("SELECT * from macs;");
	$sth->execute();
	print "<table class='macs' align='center'>
			<tr class='macs'>
				<th>Mac Name</th>
				<th>en0 MAC Address</th>
				<th>State</th>
			</tr>";
	while(my $row = $sth->fetchrow_hashref){
		print "<tr class='macs'>
				<td class='" . ($i % 2 ? "alt_1" : "alt_0") . "'><a href='?name=" . $row->{'name'} . "'>" . $row->{'name'} . "</a></td>
				<td class='" . ($i % 2 ? "alt_1" : "alt_0") . "'>" . $row->{'en0_mac_address'} . "</td>
				<td class='" . ($i % 2 ? "alt_1" : "alt_0") . "'>" . $row->{'state'} . "</td>
			</tr>\n";
	}
	$sth->finish();
	
	print "</table>\n<br /><br /><table>";
	$sth = $dbh->prepare("SELECT * FROM tracks t left outer join macs m on t.mac_id = m.id where t.type != 'check-in';");
	$sth->execute();
	while(my $row = $sth->fetchrow_hashref){
		my $airport_dump = decode_base64($row->{'airport_dump'});
		$airport_dump =~ s/^[\t\s]*//g;
			
		my $image_txt = $row->{'base_64_jpg_image'};
		my @lines = split(/ /, $row->{'base_64_jpg_image'});
		my $image;
		foreach my $line(@lines){
			$image .= decode_base64($line);
		}
		if(!(-e "/Library/WebServer/Documents/images/" . $row->{'timestamp'} . ".jpg")){
			open(IMAGE, ">/Library/WebServer/Documents/images/" . $row->{'timestamp'} . ".jpg") or die "$!";
			print IMAGE $image;
			close(IMAGE);
		}
		print "<tr class='" . ($i % 2 ? "alt_1" : "alt_0")  . "'>
			<td><table>
				<tr class='" . ($i % 2 ? "alt_1" : "alt_0")  . "'>
					<td><a href='?name=" . $row->{'name'} . "'>" . $row->{'name'} . "</a></td>
				</tr>
				<tr class='" . ($i % 2 ? "alt_1" : "alt_0")  . "'>
					<td>" . localtime($row->{'timestamp'}) . "</td>
				</tr>
				<tr class='" . ($i % 2 ? "alt_1" : "alt_0")  . "'>
					<td>" . $row->{'ip_addresses'} . "</td>
				</tr>
			</table></td>
			<td><img src='/images/" . $row->{'timestamp'} . ".jpg" . "' /></td>
			<td><textarea>" . $airport_dump . "</textarea></td></tr>\n";
		$i++;
	}
	$sth->finish();
	print "</table></body></html>\n";
	exit();
}



print "Content-type: text/xml\n\n";
my $parser = new XML::Simple (ForceArray => 1);
my $vars = $parser->XMLin($xml);

if(exists($vars->{'type'}) && $vars->{'type'} eq "track"){
	open(OUT, ">/tmp/" . time() . ".xml");
	print OUT $xml;
	close(OUT);
	my $_t = time();
	if(exists( $vars->{'timestamp'}[0]->{'value'})){
		$_t = localtime($vars->{'base_64_jpg_image'}[0]->{'value'});
	}

	my $sth = $dbh->prepare("INSERT into tracks (mac_id, timestamp, base_64_jpg_image, airport_dump, ip_addresses, type) VALUES (?, ?, ?, ?, ?, 'upload');") or die "$!";
	$sth->execute($vars->{'mac_id'}, $_t, $vars->{'base_64_jpg_image'}[0]->{'value'}, $vars->{'airport_dump'}[0]->{'value'}, $vars->{'ip_addresses'}[0]->{'value'}) or die "$!";
	$sth->finish();	
}


my $sth = $dbh->prepare("SELECT * from macs a left outer join config b on a.id = b.mac_id where a.name = ? and a.en0_mac_address = ?;") or die "$!";
$sth->execute($vars->{'name'}, $vars->{'en0_mac_address'}) or die "$!";
if(my $row = $sth->fetchrow_hashref){
	
	
	print '<?xml version="1.0" encoding="utf-8" ?>' . "\n";
	print "<status type=\"set_config\">\n";
	print "\t<config key=\"report_rate\" value=\"" . $row->{'report_rate'} . "\" />\n";
	print "\t<config key=\"speak_rate\" value=\"" . $row->{'speak_rate'} . "\" />\n";
	print "\t<config key=\"pipe_delimited_phrases\" value=\"" . $row->{'pipe_delimited_phrases'} . "\" />\n";
	print "\t<config key=\"mac_id\" value=\"" . $row->{'mac_id'} . "\" />\n";
	print "\t<config key=\"state\" value=\"" . $row->{'state'} . "\" />\n";
	print "\t<config key=\"en0_mac_address\" value=\"" . $row->{'en0_mac_address'} . "\" />\n";
	print "</status>\n\n";
	my $sth = $dbh->prepare("INSERT into tracks (mac_id, timestamp, type) VALUES (?, ?, 'check-in');") or die $!;
	$sth->execute($row->{'mac_id'}, time()) or die "$!";
	$sth->finish();
}
else{
	$sth->finish();
	$sth = $dbh->prepare("INSERT INTO macs (name, en0_mac_address, state) VALUES (?, ?, 'aok');") or die "$!";
	$sth->execute($vars->{'name'}, $vars->{'en0_mac_address'});
	$sth->finish();

	my $mac_id = $dbh->func('last_insert_rowid');

	$sth = $dbh->prepare("INSERT INTO config (mac_id, report_rate, speak_rate, pipe_delimited_phrases) VALUES (?, 300, 0, '');") or die "$!";
	$sth->execute($mac_id) or die "$!";
	$sth->finish();
	print '<?xml version="1.0" encoding="utf-8" ?><status type="created_new_entry" />';
	exit(0);
}





sub create_db
{
	open(OUTFILE, ">>$database_file") or die $fatal . "-- Cannot create $database_file";
	close(OUTFILE);
	$dbh = DBI->connect("dbi:SQLite:dbname=$database_file","","",undef) or die $fatal;
	$dbh->do("CREATE TABLE IF NOT EXISTS `macs` (`id` INTEGER PRIMARY KEY, `name` text, `en0_mac_address` text, `state` text);");
	$dbh->do("CREATE TABLE IF NOT EXISTS `tracks` (`id` INTEGER PRIMARY KEY, `type` text, `timestamp` INTEGER, `mac_id` INTEGER, `base_64_jpg_image` text, `airport_dump` text, `ip_addresses` text);");
	$dbh->do("CREATE TABLE IF NOT EXISTS `config` (`id` INTEGER PRIMARY KEY, `mac_id` INTEGER, `report_rate` INTEGER, `speak_rate` INTEGER, `pipe_delimited_phrases` text);");
	
}
